How to use Google Sheets for SEO

Google Sheets is one of the most powerful tools for SEO. It has a great variety of functionalities, and functions to manipulate your data, APIs, and documentation. If you have been using it for a few years, you may have noticed that its formulas are getting more and more complex with various concatenations, conditionals, nested functions, etc. This can sometimes be problematic as our spreadsheet becomes more and more loaded with information and running slower and slower.

What are UDFs?

Today we are going to see how to create a UDF for SEO. A UDF is a User Defined Function. We are going to create a Google Sheets function with JavaScript code that does what we want it to do, beyond the preset functions that come with the application.

 What are UDFs for?

Sometimes it is simply not possible to achieve the desired goal with the default functions; sometimes we do the same calculations so many times that it would serve us well to have a predefined functionality in advance for this. As a solution to this, Google Sheets offers the functionality to create custom functions to deal with such situations.

There are times when we need to use kilometer-long formulas in Google Sheets or Microsoft Excel that make our spreadsheet take longer to load, look messy, and even increase the margin of error. Additionally, long and concatenated formulas are not always easy to maintain and if you go back to the file a couple of weeks after not using it, you may not understand the logic implemented to build on it.

A concrete example of a user-defined function

Let’s see how we can use this functionality for a typical URL redirection or URL migration template. We are going to program a Google Sheets for SEO function that may be useful to more than one: a function that converts a cell into a slug.

Some will likely resort to something like this:

This function makes all the text in the cell lowercase and replaces all spaces with hyphens. But as we can see, it is not enough since there are special characters that are not replaced correctly. We need a UDF for SEO.

Instead of creating a long and messy formula detailing all the casuistry of replacements, let’s write a custom function for Google Sheets. To do this we need to go to the menu Tools > Script Editor

It has to open a window like the following:

In this case, we name the file ‘UDFs for SEO’ and then save it.

Everything we write inside the myFunction() function is going to be executed when we run the script. 

Let’s start with something easy to understand how it works.

Let’s change the function name to slug. This name can be anything (no spaces) and is the name of the function that will be written to the cell when the function is called.

This code reads like this: when someone calls the slug function, the input inside the parenthesis is raised. Next step, the input is now the same value but passed to lowercase; return the input.

Pretty straightforward, isn’t it? It is worth noting that the input parameter can be called anything, but whenever it is called you must always use the name you assigned to it.

Let’s see how it looks in the spreadsheet…

A success! We wrote our custom function in a cell and the output was as desired.

Now let’s complicate it a bit… let the function not only convert the text in a cell to lowercase but also replace the spaces with hyphens.

Notice that we use a regular expression to find one or more spaces in the cell. This takes care of potential double, triple spaces, etc., and replaces them with a single hyphen. Let’s see how it turned out …

Good! But what do we do with all the special characters? We simply put together a regex with all the characters we want to delete (technically we would be replacing them with nothing).

The result in the sheet will be:

We see that unwanted characters no longer appear in the output.

It’s that easy. You may have to go through some JavaScript programming sites to find what you need, but the good news is that the documentation is extensive and the communities that can give you a hand with this as well. Lose the fear and start programming your scripts for Google Sheets!

We leave the code for you to try in your spreadsheets:

function slug(input) {
  input = input.toLowerCase();
  input = input.replace(/\s+/g, "-");
  input = input.replace(/[&\/\\#,+()$~%.'":*¿?¡!<>{}]/g, '');
  return input;
}